<?php
	$odbc_connect = odbc_connect("fingerprint", "" , "");
	$mysql_connect = mysql_connect('localhost', 'hr_mysql', 'hr_mysql');
	if (($odbc_connect) && ($mysql_connect))
	{		
		//Get the last employee_id of OrangeHrm
		mysql_select_db('hr_mysql', $mysql_connect);
		$mysql_query = "select emp_number, employee_id from hs_hr_employee order by emp_number desc";
		$mysql_result = mysql_query($mysql_query);
		if(!$mysql_result)
		{
			exit;
		}
		$mysql_row = mysql_fetch_row($mysql_result);		
		$max_emp_number = $mysql_row[0];
		$max_employee_id = $mysql_row[1];
		
		//Get all employee from FingerPrint		
		$odbc_query = "select * from USERINFO";
		$odbc_row = odbc_exec($odbc_connect, $odbc_query);
		
		$emp_number = $max_emp_number;
		$employee_id = $max_employee_id;
		while(odbc_fetch_row($odbc_row))
		{			
			//Employee fetching
			$employee_name = odbc_result($odbc_row, 4);			
			$mysql_query = "select * from hs_hr_employee where emp_firstname = '$employee_name'";			
			$mysql_result = mysql_query($mysql_query);
			$num_rows = mysql_num_rows($mysql_result);			
			
			//If fetched employee does not exist
			if($num_rows == 0)
			{
				//Increment by 1
				$emp_number += 1;
				
				$employee_id += 1;
				$employee_id = "0000" . $employee_id;	
				$employee_id = substr($employee_id, 4*-1);				
				
				//Add new employee to OrangeHrm
				$mysql_query = "insert into hs_hr_employee (emp_number, employee_id, emp_lastname, emp_firstname) values ('$emp_number', '$employee_id', '', '$employee_name')";				
				mysql_query($mysql_query);
			}			
			
			mysql_free_result($mysql_result);
		}
		
		//end of employee import process
		echo 'Import employee: Done<br/>';
		
		//Get the attendance records		
		$year = "";		
		if(isset($_GET['year']))
			$year = $_GET['year'];
		else
			$year = date("Y");			
		
		$month = "";		
		if(isset($_GET['month']))
			$month = $_GET['month'];
		else
			$month = date("n");			
		
		$odbc_query = "select * from CHECKINOUT WHERE YEAR(CHECKTIME) = '$year' AND MONTH(CHECKTIME) = '$month'";
		$odbc_row = odbc_exec($odbc_connect, $odbc_query);
		while(odbc_fetch_row($odbc_row))
		{
			//Attendance fetching
			$employee_id = odbc_result($odbc_row, 1);
			$check_time = odbc_result($odbc_row, 2);
			$check_type = odbc_result($odbc_row, 3);
			$mysql_query = "select * from hs_hr_attendance_fingerprint where employee_id = '$employee_id' and check_time = '$check_time' and check_type = '$check_type'";			
			$mysql_result = mysql_query($mysql_query);
			$num_rows = mysql_num_rows($mysql_result);
			
			if($num_rows == 0)
			{
				//Add attendance record to OrangeHrm
				$mysql_query = "insert into hs_hr_attendance_fingerprint (employee_id, check_time, check_type) values ('$employee_id', '$check_time', '$check_type')";				
				mysql_query($mysql_query);
			}					
		}
		
		//end of attendance import process
		echo 'Import attendance: Done<br/>';
		
		//Pre-calculate attendance		
		$mysql_query = "delete from hs_hr_attendance_precalculate where year(check_date) = '$year' and month(check_date) = '$month'";
		mysql_query($mysql_query);
		$mysql_query = "delete from hs_hr_attendance where year(punchin_time) = '$year' and month(punchin_time) = '$month'";
		mysql_query($mysql_query);		
		
		$mysql_query = "INSERT INTO `hs_hr_attendance_precalculate` (`employee_id`, `morning_in`, `check_date`) SELECT employee_id, `morning_in_time`, `check_date` FROM `v_attendance_morning_in` WHERE year(check_date) = '$year' and month(check_date) = '$month'";
		mysql_query($mysql_query);
		
		$mysql_query = "INSERT INTO `hs_hr_attendance_precalculate` (`employee_id`, `morning_out`, `check_date`) SELECT employee_id, `morning_out_time`, `check_date` FROM `v_attendance_morning_out` WHERE year(check_date) = '$year' and month(check_date) = '$month'";
		mysql_query($mysql_query);

		$mysql_query = "INSERT INTO `hs_hr_attendance_precalculate` (`employee_id`, `afternoon_in`, `check_date`) SELECT employee_id, `afternoon_in_time`, `check_date` FROM `v_attendance_afternoon_in` WHERE year(check_date) = '$year' and month(check_date) = '$month'";
		mysql_query($mysql_query);

		$mysql_query = "INSERT INTO `hs_hr_attendance_precalculate` (`employee_id`, `afternoon_out`, `check_date`) SELECT employee_id, `afternoon_out_time`, `check_date` FROM `v_attendance_afternoon_out` WHERE year(check_date) = '$year' and month(check_date) = '$month'";
		mysql_query($mysql_query);
		echo 'Pre-calculate attendance: Done<br/>';

		//Max attendance 		
		$mysql_query = "select attendance_id from hs_hr_attendance order by attendance_id desc limit 1";		
		$mysql_result = mysql_query($mysql_query);
		if(!$mysql_result)
			exit;
		$mysql_row = mysql_fetch_row($mysql_result);		
		$max_attendance_id = $mysql_row[0];		
		
		$mysql_query = 
			"SELECT 
				employee_id,
				MIN(morning_in) AS morning_in,
				MIN(morning_out) AS morning_out,
				MIN(afternoon_in) AS afternoon_in,
				MIN(afternoon_out) AS afternoon_out
			FROM 
				`hs_hr_attendance_precalculate`
			GROUP BY
				employee_id,
				check_date
			ORDER BY 
				employee_id,
				check_date";
		$mysql_result = mysql_query($mysql_query);
		if(!$mysql_result)
			exit;
		
		$attendance_id = $max_attendance_id;
		while($mysql_row = mysql_fetch_array($mysql_result)) 
		{
			$employee_id = $mysql_row[0];
			
			$morning_in = strlen($mysql_row[1]) != 0 ? "'" . mysql_escape_string($mysql_row[1]) . "'" : "null";
			$morning_out = strlen($mysql_row[2]) != 0 ? "'" . mysql_escape_string($mysql_row[2]) . "'" : "null";
			$afternoon_in = strlen($mysql_row[3]) != 0 ? "'" . mysql_escape_string($mysql_row[3]) . "'" : "null";
			$afternoon_out = strlen($mysql_row[4]) != 0 ? "'" . mysql_escape_string($mysql_row[4]) . "'" : "null";
			//$timestamp_diff = 0;
	
			//$mysql_query = "select * from hs_hr_attendance where employee_id = '$employee_id' and punchin_time = '$punchin_time' and punchout_time = '$punchout_time'";
			//$attendance_result = mysql_query($mysql_query);
			//$num_rows = mysql_num_rows($attendance_result);
			
			//if($num_rows == 0)
			//{
				$attendance_id += 1;
				//Add new attendance record to OrangeHrm
				
				$mysql_query = 
					"insert into hs_hr_attendance (attendance_id, employee_id, punchin_time, punchout_time, timestamp_diff, status, in_note, out_note) values ('$attendance_id', ". 
					"'$employee_id', ". 
					"$morning_in, " . 
					"$afternoon_out, " . 
					" 0, " . 
					"'1', " . 
					"$morning_out, " . 
					"$afternoon_in)";				
				mysql_query($mysql_query);
			//}			
		}	

		//end of attendance calculation process
		echo 'Calculate attendance: Done<br/>';		
	}
	
	mysql_close($mysql_connect);
	odbc_close($odbc_connect);
?>